﻿


/****** Object:  Stored Procedure dbo.GenListaRezultat    Script Date: 19.05.2006 3:01:29 PM ******/
CREATE PROCEDURE [dbo].[GenListaVenituriSiCheltuieli]  AS

DECLARE @dataUI as float, @dtDataUI as datetime
DECLARE @tabela varchar(100)
DECLARE @strData varchar(100)
DECLARE @fni	bit
DECLARE @idLista int

SET @fni = 0
SET @idLista=15

DECLARE @CodUnitate smallint
SET @CodUnitate=0
SELECT @CodUnitate=CAST(Valoare AS smallint) FROM [SetariLocale] WHERE [CodSetare]='UCRT' AND [xUser]=suser_sname() AND [xHost]=host_name()


SELECT @strData=Valoare  FROM [ParamListe] WHERE IDLista=@idLista AND Cod='LDT' AND [User]=suser_sname() AND [Host]=host_name() AND [CodUnitate]=@CodUnitate
SET @strData = LTRIM(@strData)
SET @strData = RTRIM(@strData)

IF @strData is NULL  GOTO EROARE
IF LEN(@strData)<>0
BEGIN
	SET @dtDataUI = CAST(CAST(@strData AS float)  AS datetime)
END
SELECT @strData=Valoare  FROM [ParamListe] WHERE IDLista=@idLista AND Cod='FNI' AND [User]=suser_sname() AND [Host]=host_name()  AND [CodUnitate]=@CodUnitate
IF @strData='1'
	SET @fni = 1



IF  EXISTS(SELECT * FROM tempdb..SysObjects WHERE name = '#balanta' AND type = 'U')	
BEGIN
	DROP TABLE  #balanta
	IF @@ERROR != 0 GOTO EROARE				
END

SELECT c.Cont, C.Denumire,  C.Tip,  C.ContParinte,C.Terminal, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit], 
	DebitPrecedent [DebPrec], CreditPrecedent [CrePrec],  
	sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
INTO [#balanta_t]
	FROM [Conturi]  c
LEFT OUTER JOIN 
	(SELECT sum(sumaDebit) AS DebCurent, sum(sumaCredit) AS CreCurent, Cont, CodUnitate,IDJurnal FROM
			(SELECT 			    
				coalesce(SUM(CASE WHEN D.DataDoc<=@dtDataUI THEN F.ValoareLei ELSE 0 END),0) sumaDebit, 
				0.00 AS sumaCredit, 
				F.ContDebit AS Cont,D.CodUnitate AS CodUnitate,D.IdJurnal AS Idjurnal
				FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
				WHERE d.[CodUnitate] = @CodUnitate
                GROUP BY F.ContDebit,D.CodUnitate,D.IDJurnal
			UNION
			SELECT 
				0.00 AS sumaDebit,
				coalesce(SUM(CASE WHEN D.DataDoc<=@dtDataUI THEN F.ValoareLei ELSE 0 END),0) sumaCredit, 
                F.ContCredit AS Cont,D.CodUnitate  AS CodUnitate,D.IDJurnal AS Idjurnal
				FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
				WHERE d.[CodUnitate] = @CodUnitate
				GROUP BY F.ContCredit,D.CodUnitate,D.IDJurnal) t1

GROUP BY Cont,CodUnitate,IDJurnal) RUL 
ON dbo.IsContInclus(c.cont,rul.cont,c.terminal)=1
WHERE  c.[Cont]<>'N/A'  AND Categorie=0  AND c.[CodUnitate]=@CodUnitate
and (DebCurent <> 0 OR DebitInitial <> 0 OR DebitPrecedent <> 0 OR CreCurent <> 0 OR CreditInitial <> 0 OR CreditPrecedent <> 0)
AND ((c.Cont LIKE '6%') OR (c.Cont LIKE '7%') OR (c.Cont LIKE '1%'))
GROUP BY  c.Cont, C.Denumire,  C.Tip,  C.ContParinte,C.Terminal,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent
ORDER BY c.Cont 


SELECT  Cont,ContParinte,Terminal,Denumire,Tip,DebInit,CreInit, DebPrec,CrePrec, DebCurent, CreCurent,
	DebPrec+DebCurent DebRul,
	CrePrec+CreCurent CreRul,
	dbo.SoldDebit(Tip, DebInit, CreInit) SoldInitDeb, 
	dbo.SoldCredit(Tip, DebInit, CreInit) SoldInitCre, 
	dbo.Sold(Tip, DebInit, CreInit) SoldInit, 
	dbo.SoldDebit(Tip, DebPrec, CrePrec) SoldPrecDeb, 
	dbo.SoldCredit(Tip, DebPrec, CrePrec) SoldPrecCre, 
	dbo.Sold(Tip, DebPrec, CrePrec) SoldPrec, 
	dbo.SoldDebit(Tip, DebCurent, CreCurent) SoldCurentDeb, 
	dbo.SoldCredit(Tip, DebCurent, CreCurent) SoldCurentCre, 
	dbo.Sold(Tip, DebCurent, CreCurent) SoldCurent,
	DebInit + DebPrec+ COALESCE(DebCurent, 0) DebTotal, CreInit + CrePrec + COALESCE(CreCurent, 0) CreTotal, 
	dbo.SoldDebit(Tip, DebInit + DebPrec +COALESCE(DebCurent, 0), CreInit + CrePrec+COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrec+ COALESCE(DebCurent, 0), CreInit + CrePrec+COALESCE(CreCurent, 0)) SoldTotalCre,
	dbo.Sold(Tip, DebInit + DebPrec+COALESCE(DebCurent, 0), CreInit + CrePrec+COALESCE(CreCurent, 0)) SoldTotal
INTO [#balanta] FROM [#balanta_t]


SELECT Tip,Cont,DebInit,CreInit, SoldInit, DebRul, CreRul, SoldTotal,DebRul,CreRul FROM [#balanta] 
ORDER BY Cont

GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balansei',16,-1)
Gata: